﻿package net.ojbk.jiaofu.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import net.ojbk.database.DatabaseConnection;
import net.ojbk.jiaofu.po.SyTaskSubmit;
public class SyTaskSubmitDao {
	static final String tableName="syTaskSubmit";
	DatabaseConnection dbconn;
	Statement stmt;
	public SyTaskSubmitDao(DatabaseConnection dbconn) {
		this.dbconn = dbconn;
		stmt=dbconn.getStatement();
	}
	public boolean addOneRecord(SyTaskSubmit sts) {  //yes
		if (sts == null) return false;
		if (sts.getUserId() == null ||sts.getTaskId() == null) return false; 
		boolean flag=false;
		try {
			String sql="insert into "
					+ tableName
					+ "(userId,taskId,status) value('"
					+ sts.getUserId()
					+"','"
					+ sts.getTaskId()
					+ "','"
					+ sts.getStatus()    //status应默认设置为空
//					+ "', "
//					+ sts.getSubmitTime()   //SubmitTime应可设为空
					+ "')";
			System.out.println(sql);
			stmt.executeUpdate(sql);
			flag=true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
		return flag;
	}
	public boolean deleteOneRecord(SyTaskSubmit sts) { // is OK? ：yes
		if (sts == null) return false;
		if (sts.getUserId() == null ||sts.getTaskId() == null) return false; // 主码不能为空
		boolean flag = false;
		try {
			String sql = "DELETE FROM "
					+ tableName
					+ " WHERE userId='"
					+ sts.getUserId()
					+ "' AND taskId='"
					+ sts.getTaskId()
					+ "';";
			stmt.executeUpdate(sql);
			flag = true;
		} catch(Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	public boolean updateOneRecord(SyTaskSubmit sts) {    //yes
		if (sts == null) return false;
		if (sts.getUserId()== null||sts.getTaskId()==null) return false; // 主码不能为空
		boolean flag = false;
		try {
			// UPDATE userData SET id='10001', email='200562421@qq.com', tel='12345678901', idcard='362226199711200017', grade='2016', college='软件学院', clas='软件2016-2', registrationTime='2018.3.29', lastIP='192.168.1.1' WHERE id='10001';
			String sql = "UPDATE "
					+ tableName
					+ " SET "
					+ "status='"
					+ sts.getStatus()
					+ "' where userId='"
					+ sts.getUserId()
					+ "' and taskId='"
					+ sts.getTaskId()
					+ "';";
			stmt.executeUpdate(sql);
			return true;
		} catch(Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	public boolean queryOneRecord(SyTaskSubmit sts) { // is OK? ：yes
		if (sts == null) return false;
		if (sts.getUserId()== null||sts.getTaskId()==null) return false; // 主码不能为空
		boolean flag = false;
		ResultSet re;
		try {
			String sql="select * from "
					+ tableName
					+ " where userId='"
					+sts.getUserId()
					+"'and taskId='"
					+sts.getTaskId()
					+"';";
			re = stmt.executeQuery(sql);
			if (re.next()) {
				sts.setUserId(re.getString("userId"));
				sts.setTaskId(re.getString("taskId"));
				sts.setStatus(re.getInt("status"));
				sts.setSubmitTime(re.getTimestamp("submitTime"));
				flag = true;
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	public int getCount() { // is OK? ：yes
		int count = 0;
		ResultSet re;
		try {
			// SELECT COUNT(*) FROM userData
			String sql="SELECT COUNT(*) FROM "
					+ tableName
					+ ";";
			re = stmt.executeQuery(sql);
			if (re.next()) {
				count = re.getInt(1);
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return count;
	}
	public int getCountByUserId(String userId) { // is OK? ：yes
		int count = 0;
		ResultSet re;
		try {
			// SELECT COUNT(*) FROM userData
			String sql="SELECT COUNT(*) FROM "
					+ tableName
					+ " where userId='"
					+ userId
					+ "';";
			re = stmt.executeQuery(sql);
			if (re.next()) {
				count = re.getInt(1);
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return count;
	}
	public int getCountByTaskId(String taskId) { // is OK? ：yes
		int count = 0;
		ResultSet re;
		try {
			// SELECT COUNT(*) FROM userData
			String sql="SELECT COUNT(*) FROM "
					+ tableName
					+ " where taskId='"
					+ taskId
					+ "';";
			re = stmt.executeQuery(sql);
			if (re.next()) {
				count = re.getInt(1);
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return count;
	}
	public SyTaskSubmit[] queryAllRecodByUserId(String userId){ // is OK? ：yes
		int count = this.getCountByUserId(userId);
		SyTaskSubmit[] syts=new SyTaskSubmit[count]; 
		ResultSet re;
		SyTaskSubmit sts;
		try {
			// SELECT * FROM userData
			String sql="SELECT * FROM "
					+ tableName
					+ " where userId='"
					+ userId
					+ "';";
			re = stmt.executeQuery(sql); 
			for (int i=0; re.next(); i++) {
				sts = new SyTaskSubmit();
				// 赋值代码
				sts.setUserId(re.getString("userId"));
				sts.setTaskId(re.getString("taskId"));
				sts.setStatus(re.getInt("status"));
				sts.setSubmitTime(re.getTimestamp("submitTime"));
				syts[i] = sts;
			}
		} catch(Exception e) {
			e.printStackTrace();
			return syts;
		}
		return syts;
	}
	public SyTaskSubmit[] queryAllRecodByTaskId(String taskId){ // is OK? ：yes
		int count = this.getCountByTaskId(taskId);
		 SyTaskSubmit[] syts=new  SyTaskSubmit[count]; 
		ResultSet re;
		 SyTaskSubmit sts;
		try {
			// SELECT * FROM userData
			String sql="SELECT * FROM "
					+ tableName
					+ " where taskId='"
					+ taskId
					+ "';";
			System.out.println(sql);
			re = stmt.executeQuery(sql); 
			for (int i=0; re.next(); i++) {
				sts = new SyTaskSubmit();
				
				// 赋值代码
				
				sts.setUserId(re.getString("userId"));
				sts.setTaskId(re.getString("taskId"));
				sts.setStatus(re.getInt("status"));
				sts.setSubmitTime(re.getTimestamp("submitTime"));
				syts[i] = sts;
			}
		} catch(Exception e) {
			e.printStackTrace();
			return syts;
		}
		return syts;
	}
	public boolean checkOneRecord(String taskId, String userId) { // is OK? ：yes
		boolean flag = false;
		ResultSet re;
		if(taskId==null||userId==null)return false;
		try {
			String sql="select * from "
					+ tableName
					+ " where taskId='"
					+ taskId
					+ "' AND userId='"
					+ userId
					+ "';";
			System.out.println(sql);
			re = stmt.executeQuery(sql);
			if (re.next()) {
				flag = true;
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return flag;
	}
	public SyTaskSubmit[] queryAllRecordByStatus(String taskId) {
		int count = this.getCountByStatus(taskId);
		 SyTaskSubmit[] syts=new  SyTaskSubmit[count]; 
		ResultSet re;
		 SyTaskSubmit sts;
		try {
			// SELECT * FROM userData
			String sql="SELECT * FROM "
					+ tableName
					+ " where taskId='"
					+ taskId
					+ "' and status=0"
					+ ";";
			System.out.println(sql);
			re = stmt.executeQuery(sql); 
			for (int i=0; re.next(); i++) {
				sts = new SyTaskSubmit();
				
				// 赋值代码
				
				sts.setUserId(re.getString("userId"));
				sts.setTaskId(re.getString("taskId"));
				sts.setStatus(re.getInt("status"));
				sts.setSubmitTime(re.getTimestamp("submitTime"));
				syts[i] = sts;
			}
		} catch(Exception e) {
			e.printStackTrace();
			return syts;
		}
		return syts;
	}
	public int getCountByStatus(String taskId) {
		int count = 0;
		ResultSet re;
		try {
			// SELECT COUNT(*) FROM userData
			String sql="SELECT COUNT(*) FROM "
					+ tableName
					+ " where taskId='"
					+ taskId
					+ "' and status=0"
					+ ";";
			System.out.print(sql);
			re = stmt.executeQuery(sql);
			if (re.next()) {
				count = re.getInt(1);
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return count;
	}
	public int getCountByUserIdStatus(String userId) {
		int count = 0;
		ResultSet re;
		try {
			// SELECT COUNT(*) FROM userData
			String sql="SELECT COUNT(*) FROM "
					+ tableName
					+ " where userId='"
					+ userId
					+ "' and status=0"
					+ ";";
			System.out.print(sql);
			re = stmt.executeQuery(sql);
			if (re.next()) {
				count = re.getInt(1);
			}
		} catch(Exception e) {
			e.printStackTrace();
		}
		return count;
	}
	public SyTaskSubmit[] getAllByUserIdStatus(String userId) {
		SyTaskSubmit syts[]=new SyTaskSubmit[getCountByUserIdStatus(userId)]; 
		ResultSet re;
		 SyTaskSubmit sts;
		try {
			// SELECT * FROM userData
			String sql="SELECT * FROM "
					+ tableName
					+ " where userId='"
					+ userId
					+ "' and status=0"
					+ ";";
			System.out.println(sql);
			re = stmt.executeQuery(sql); 
			for (int i=0; re.next(); i++) {
				sts = new SyTaskSubmit();
				// 赋值代码
				sts.setUserId(re.getString("userId"));
				sts.setTaskId(re.getString("taskId"));
				sts.setStatus(re.getInt("status"));
				sts.setSubmitTime(re.getTimestamp("submitTime"));
				syts[i] = sts;
			}
		} catch(Exception e) {
			e.printStackTrace();
			return syts;
		}
		return syts;

	}
	public static void main(String args[]) {
//		DatabaseConnection dbconn=new DatabaseConnection();
//		SyTaskSubmitDao sytsd=new SyTaskSubmitDao(dbconn);
//		SyTaskSubmit sts[];
//		String userId="0000";
//		sts=sytsd.getAllByUserIdStatus(userId);
//		for(int i=0;i<sts.length;i++) {
//			System.out.println(sts[i].getTaskId());
//		}
//		dbconn.close();
	}
}
